﻿using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Easy.Common;

namespace Easy.DAL
{
    public class SystemUserDAL
    {
        private readonly SqlDatabase sqldb = new SqlDatabase(Config.TulingBuyCrmConnectString);


        /// <summary>
        /// 使用用户名或工号、密码获取用户信息
        /// </summary>
        /// <param name="username">用户名或工号</param>
        /// <param name="password">MD5加密后的密码</param>
        /// <returns>用户所有字段</returns>
        public DataTable GetSystemUserByPwd(string username, string password)
        {
            string sql = "select top 1 * from SystemUser as S where (S.UserName=@username) and S.[Password]=@password and isnull(S.DeletionStateCode,0)=0 ";
            SqlParameter[] param = new SqlParameter[] { new SqlParameter("@username", username), new SqlParameter("@password", password) };
            DbCommand cmd = sqldb.GetSqlStringCommand(sql);
            cmd.Parameters.AddRange(param);
            DataTable dt = sqldb.ExecuteDataSet(cmd).Tables[0];
            return dt;
        }
        
        public DataTable GetSystemUserById(Guid systemUserId)
        {
            string sql = "select * from SystemUser where SystemUserId=@uid";
            SqlParameter param = new SqlParameter("@uid", systemUserId);
            DbCommand cmd = sqldb.GetSqlStringCommand(sql);
            cmd.Parameters.Add(param);
            DataTable dt = sqldb.ExecuteDataSet(cmd).Tables[0];
            return dt;
        }

        public DataTable GetSystemUserByUserName(string username)
        {
            string sql = "select * from SystemUser where UserName=@username";
            SqlParameter param = new SqlParameter("@username", username);
            DbCommand cmd = sqldb.GetSqlStringCommand(sql);
            cmd.Parameters.Add(param);
            DataTable dt = sqldb.ExecuteDataSet(cmd).Tables[0];
            return dt;
        }

     
        /// <summary>
        /// 修改systemuser密码
        /// </summary>
        /// <param name="SystemUserId">用户GUID</param>
        /// <param name="pass">MD5加密后的密码</param>
        /// <returns></returns>
        public int Updatesystemuserpass(Guid SystemUserId, string pass)
        {
            string sql = "update SystemUser set Password=@pass where SystemUserId=@uid";
            SqlParameter[] param = new SqlParameter[] { 
            new SqlParameter("@pass",pass),
            new SqlParameter("@uid",SystemUserId)
            };
            DbCommand com = sqldb.GetSqlStringCommand(sql);
            com.Parameters.AddRange(param);
            int i = sqldb.ExecuteNonQuery(com);
            return i;
        }
    }
}
